# models.py

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


# 一对多关系
def test_one_to_many_tables():
    """
    一对多关系
    :return:
    """

    class AnalysisStep(Base):
        __tablename__ = 'analysis_steps'

        id = Column(Integer, primary_key=True)
        step_name = Column(String)

        parameters = relationship("AnalysisParameter", back_populates="process")

    class AnalysisParameter(Base):
        __tablename__ = 'analysis_parameters'

        id = Column(Integer, primary_key=True)
        step_id = Column(Integer, ForeignKey('analysis_steps.id'))
        parameter_name = Column(String)

        process = relationship("AnalysisStep", back_populates="parameters")

    # database.py

    from sqlalchemy import create_engine
    from sqlalchemy.orm import sessionmaker

    def get_engine():
        engine = create_engine('sqlite:///analysis.db', echo=True)
        return engine

    def create_session():
        engine = get_engine()
        Base.metadata.create_all(engine)
        Session = sessionmaker(bind=engine)
        return Session()

    def main():
        session = create_session()

        # 插入分析步骤
        step1 = AnalysisStep(step_name='Step 1')
        step2 = AnalysisStep(step_name='Step 2')
        session.add_all([step1, step2])
        session.commit()

        # 插入参数
        param1 = AnalysisParameter(step_id=step1.id, parameter_name='Param 1')
        param2 = AnalysisParameter(step_id=step2.id, parameter_name='Param 2')
        session.add_all([param1, param2])
        session.commit()

        # 查询并打印步骤及其参数
        steps = session.query(AnalysisStep).all()
        for step in steps:
            print(f"Step Name: {step.step_name}")
            for param in step.parameters:
                print(f"  Parameter Name: {param.parameter_name}")

        # 查询并打印参数及其所属步骤
        params = session.query(AnalysisParameter).all()
        for param in params:
            print(f"Parameter Name: {param.parameter_name}, Belongs to Step: {param.process.step_name}")


# SQLAlchemy 中的各种数据库查询操作
def test_query_methods():
    """
    SQLAlchemy 中的各种数据库查询操作
    :return:
    """
    from sqlalchemy import create_engine, Column, Integer, String
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker, aliased
    from sqlalchemy.sql import func

    # 假设这里有一个数据库引擎
    engine = create_engine('sqlite:///example.db')
    Base = declarative_base()

    class AnalysisParameter(Base):
        __tablename__ = 'analysis_parameters'
        id = Column(Integer, primary_key=True)
        name = Column(String)
        value = Column(Integer)

    # 创建表
    Base.metadata.create_all(engine)

    # 创建会话
    Session = sessionmaker(bind=engine)
    session = Session()

    # 查询示例
    params = session.query(AnalysisParameter).all()
    print("All parameters:", params)

    # 条件筛选
    filtered_params = session.query(AnalysisParameter).filter(AnalysisParameter.id == 1).all()
    print("Filtered parameters:", filtered_params)

    # 获取第一条记录
    first_param = session.query(AnalysisParameter).first()
    print("First parameter:", first_param)

    # 获取单条记录
    param = session.query(AnalysisParameter).get(1)
    print("Single parameter:", param)

    # 排序查询
    sorted_params = session.query(AnalysisParameter).order_by(AnalysisParameter.value.desc()).all()
    print("Sorted parameters:", sorted_params)

    # 限制查询结果数量
    limited_params = session.query(AnalysisParameter).limit(10).all()
    print("Limited parameters:", limited_params)

    # 偏移量查询
    offset_params = session.query(AnalysisParameter).offset(10).limit(10).all()
    print("Offset parameters:", offset_params)

    # 连接查询
    AnalysisResult = aliased(AnalysisParameter)
    joined_params = session.query(AnalysisParameter).join(AnalysisResult,
                                                          AnalysisParameter.id == AnalysisResult.id).all()
    print("Joined parameters:", joined_params)

    # 子查询
    subquery = session.query(AnalysisParameter.id).filter(AnalysisParameter.id > 10).subquery()
    subquery_params = session.query(AnalysisParameter).filter(AnalysisParameter.id.in_(subquery)).all()
    print("Subquery parameters:", subquery_params)

    # 存在性查询
    exists_query = session.query(AnalysisParameter.id).filter(AnalysisParameter.id == 1).exists()
    result = session.query(exists_query).scalar()
    print("Exists:", result)

    # 计数查询
    count = session.query(AnalysisParameter).filter(AnalysisParameter.id > 10).count()
    print("Count:", count)

    # 去重查询
    distinct_values = session.query(AnalysisParameter.name).distinct().all()
    print("Distinct values:", distinct_values)

    # 分组查询
    grouped_params = session.query(AnalysisParameter.name, func.count(AnalysisParameter.id)).group_by(
        AnalysisParameter.name).all()
    print("Grouped parameters:", grouped_params)

    # 合并查询结果
    query1 = session.query(AnalysisParameter).filter(AnalysisParameter.id > 10)
    query2 = session.query(AnalysisParameter).filter(AnalysisParameter.id < 5)
    combined_query = query1.union(query2)
    combined_params = combined_query.all()
    print("Combined parameters:", combined_params)

    # 分组后的筛选
    grouped_filtered_params = session.query(AnalysisParameter.name, func.count(AnalysisParameter.id)).group_by(
        AnalysisParameter.name).having(func.count(AnalysisParameter.id) > 10).all()
    print("Grouped and filtered parameters:", grouped_filtered_params)


# 删除表中数据
def test_delete_methods():
    """
    在使用 SQLAlchemy 操作 SQLite 数据库时，删除数据主要有几种常见的方式。以下是详细的说明和
    session.query().delete()：适用于删除单个表中的所有数据或特定数据。
    session.execute() 和 SQL 语句：更灵活，可以直接执行 SQL 语句。
    session.bulk_delete_mappings()：适用于批量删除大量数据，效率更高。
    session.execute() 和文本 SQL 语句：适合复杂的删除操作。
    session.execute() 和 ORM 表达式：结合了 ORM 和 SQL 语句的优点。
    选择合适的方式取决于具体的场景和需求。通常情况下，session.query().delete() 是最常用且简单的方式。
    :return:
    """
    from sqlalchemy import create_engine, Column, Integer, String
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker

    # 创建数据库引擎
    engine = create_engine('sqlite:///example.db')
    Base = declarative_base()

    # 定义表模型
    class User(Base):
        __tablename__ = 'users'
        id = Column(Integer, primary_key=True)
        name = Column(String)

    # 创建表
    Base.metadata.create_all(engine)

    # 创建会话
    Session = sessionmaker(bind=engine)
    session = Session()

    # 插入一些数据
    session.add_all([
        User(id=1, name='Alice'),
        User(id=2, name='Bob'),
        User(id=3, name='Charlie')
    ])
    session.commit()

    # 删除所有数据
    session.query(User).delete()
    session.commit()

    # 删除特定数据
    session.query(User).filter(User.id == 2).delete()
    session.commit()

    # 删除所有数据
    session.execute(User.__table__.delete())
    session.commit()

    # 删除特定数据
    session.execute(User.__table__.delete().where(User.id == 2))
    session.commit()

    # 批量删除特定数据
    to_delete = [{'id': 1}, {'id': 3}]
    session.bulk_delete_mappings(User, to_delete)
    session.commit()

    # 删除所有数据
    session.execute("DELETE FROM users")
    session.commit()

    # 删除特定数据
    session.execute("DELETE FROM users WHERE id = :id", {'id': 2})
    session.commit()

    # 删除所有数据
    session.execute(User.__table__.delete())
    session.commit()

    # 删除特定数据
    session.execute(User.__table__.delete().where(User.id == 2))
    session.commit()


# 查询子表
def test_subquery():
    """
    直接访问关系属性：通过定义的关系属性直接访问子表项。
    使用 join 查询：通过 join 来查询子表项。
    使用 subquery 查询：通过 subquery 来查询子表项。
    使用 options 加载子表项：通过 options 加载子表项，提高查询效率。
    这些方法都可以有效地查询与指定 ParentTable 表项关联的所有子表项。选择合适的方法取决于具体的需求和性能要求。
    :return:
    """
    from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker, relationship

    Base = declarative_base()

    # 定义父类
    class ParentTable(Base):
        __tablename__ = 'parent_table'

        id = Column(Integer, primary_key=True)
        name = Column(String, unique=True)
        children = relationship("ChildrenTable", back_populates="parent", cascade="all, delete, delete-orphan")

    # 定义子类
    class ChildrenTable(Base):
        __tablename__ = 'children_table'

        id = Column(Integer, primary_key=True)
        name = Column(String)
        parent_id = Column(Integer, ForeignKey('parent_table.id'))
        __table_args__ = (UniqueConstraint('name', 'parent_id', name='uq_name_parent_id'),)
        parent = relationship("ParentTable", back_populates="children")
        grandchildren = relationship("GrandChildrenTable", back_populates="child", cascade="all, delete, delete-orphan")

    # 定义孙子类
    class GrandChildrenTable(Base):
        __tablename__ = 'grandchildren_table'

        id = Column(Integer, primary_key=True)
        name = Column(String)
        child_id = Column(Integer, ForeignKey('children_table.id'))
        __table_args__ = (UniqueConstraint('name', 'child_id', name='uq_name_child_id'),)
        child = relationship("ChildrenTable", back_populates="grandchildren")

    # 创建数据库引擎和会话
    engine = create_engine('sqlite:///example.db')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()

    # 插入一些数据
    parent = ParentTable(name='Parent1')
    child1 = ChildrenTable(name='Child1', parent=parent)
    child2 = ChildrenTable(name='Child2', parent=parent)
    grandchild1 = GrandChildrenTable(name='GrandChild1', child=child1)
    grandchild2 = GrandChildrenTable(name='GrandChild2', child=child1)
    session.add_all([parent, child1, child2, grandchild1, grandchild2])
    session.commit()

    # 1. 直接访问关系属性
    # 查询指定父表项的所有子表项
    parent = session.query(ParentTable).filter_by(name='Parent1').first()
    if parent:
        print("Children of Parent1:")
        for child in parent.children:
            print(f"  Child: {child.name}")
            print(f"  Grandchildren:")
            for grandchild in child.grandchildren:
                print(f"    Grandchild: {grandchild.name}")
    else:
        print("No parent found.")

    # 2.使用join查询
    # 查询指定父表项的所有子表项
    parent = session.query(ParentTable).filter_by(name='Parent1').first()
    if parent:
        children = (
            session.query(ChildrenTable)
            .join(ParentTable, ChildrenTable.parent_id == ParentTable.id)
            .filter(ParentTable.name == 'Parent1')
            .all()
        )
        print("Children of Parent1:")
        for child in children:
            print(f"  Child: {child.name}")
    else:
        print("No parent found.")

    # 3. 使用 subquery 查询
    # 创建子查询
    subquery = (
        session.query(ChildrenTable)
        .join(ParentTable, ChildrenTable.parent_id == ParentTable.id)
        .filter(ParentTable.name == 'Parent1')
        .subquery()
    )

    # 查询子表项
    children = session.query(subquery).all()
    print("Children of Parent1:")
    for child in children:
        print(f"  Child: {child.name}")

    # 4. 使用 options 加载子表项
    from sqlalchemy.orm import joinedload

    # 查询指定父表项及其子表项
    parent = (
        session.query(ParentTable)
        .options(joinedload(ParentTable.children).joinedload(ChildrenTable.grandchildren))
        .filter_by(name='Parent1')
        .first()
    )

    if parent:
        print("Children of Parent1:")
        for child in parent.children:
            print(f"  Child: {child.name}")
            print(f"  Grandchildren:")
            for grandchild in child.grandchildren:
                print(f"    Grandchild: {grandchild.name}")
    else:
        print("No parent found.")


if __name__ == '__main__':
    pass
